package org.yunai.swjg.document;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jumpmind.symmetric.csv.CsvWriter;
import org.yunai.yfserver.util.CsvUtil;
import org.yunai.yfserver.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * EXCEL转CSV的转换器
 * User: yunai
 * Date: 13-6-21
 * Time: 下午9:15
 */
public class XLS2CSVConvert {

    /**
     * 正则，用于匹配0串
     */
    private static final Pattern PATTERN_ZEROS = Pattern.compile("[0]*");
    /**
     * 输入Excel所在目录的顶层
     */
    private static final String INPUT = "D:\\Java\\Story\\SWJG_DEMO\\Document\\数据模版";
    /**
     * 输出CSV所在目录的顶层
     */
    private static final String OUTPUT = "D:\\Java\\Story\\SWJG_DEMO\\GameServer\\src\\main\\resources\\csv";
    /**
     * 子目录对应关系
     */
    private static Map<String, String> subPaths = new HashMap<>();

    static {
        subPaths.put("任务", "quest");
        subPaths.put("伙伴", "partner");
        subPaths.put("副本", "rep");
        subPaths.put("场景", "scene");
        subPaths.put("怪物", "monster");
        subPaths.put("技能", "skill");
        subPaths.put("活动", "activity");
        subPaths.put("玩家", "player");
        subPaths.put("道具", "item");
    }

    public static void main(String[] args) throws IOException {
        File input = new File(INPUT);
        for (Map.Entry<String, String> subPathEntry : subPaths.entrySet()) {
            // 创建输出子文件夹
            File subOutputFile = new File(OUTPUT + File.separator + subPathEntry.getValue());
            subOutputFile.delete();
            subOutputFile.mkdirs();
            // 遍历输入子文件夹
            File subFile = new File(input.getPath() + File.separator + subPathEntry.getKey());
            for (String fileName : subFile.list()) {
                // 创建CSV文件
                File outFile = new File(subOutputFile.getPath() + File.separator + genCSVFileName(fileName));
                outFile.createNewFile();
                // 输出
                File inputFile = new File(subFile + File.separator + fileName);
                convert(inputFile, outFile);
            }
        }
    }

    /**
     * 将文件名转化为csv后缀
     *
     * @param fileName 文件名
     * @return csv后缀文件名
     */
    private static String genCSVFileName(String fileName) {
        int index = fileName.lastIndexOf('.');
        if (index < 0) {
            throw new IllegalArgumentException("文件格式错误:" + fileName);
        }
        return fileName.substring(0, index) + ".csv";
    }

    /**
     * 进行excel文件转化为excel文件l
     *
     * @param excel 输入excel文件
     * @param csv   输出excel文件
     * @throws IOException
     */
    private static void convert(File excel, File csv) throws IOException {
        // 读取excel为一个数组
        List<List<String>> records = readExcel(excel);
        // 输出csv文件
        CsvWriter writer = CsvUtil.createSysWriter(csv.getPath());
        try {
            for (List<String> record : records) {
                for (String value : record) {
                    writer.write(value);
                }
                writer.endRecord();
            }
        } finally {
            writer.close();
        }
    }

    /**
     * 读取Excel，并返回数据字符串数组
     *
     * @param file excel文件
     * @return 数据字符串数组
     * @throws IOException 当file为不支持的文件类型
     */
    public static List<List<String>> readExcel(File file) throws IOException {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        switch (extension) {
            case "xls":
                return read2003Excel(file);
            case "xlsx":
                return read2007Excel(file);
            default:
                throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取 office 2003 excel
     */
    private static List<List<String>> read2003Excel(File file) throws IOException {
        List<List<String>> list = new LinkedList<>();
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        int recordValueSize = sheet.getRow(sheet.getFirstRowNum()).getLastCellNum(); // 每行记录字段数，防止有些行，有很多空记录
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<String> linked = new LinkedList<>();
            for (int j = row.getFirstCellNum(); j < recordValueSize; j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    linked.add("");
                } else {
                    String value = cell.toString();
                    String valueInt = parseInt(value);
                    if (valueInt != null) {
                        linked.add(valueInt);
                    } else {
                        linked.add(value);
                    }
                }
            }
            list.add(linked);
        }
        return list;
    }


    /**
     * 读取Office 2007 excel
     */
    private static List<List<String>> read2007Excel(File file) throws IOException {
        List<List<String>> list = new LinkedList<>();
        XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = xwb.getSheetAt(0);
        int recordValueSize = sheet.getRow(sheet.getFirstRowNum()).getLastCellNum(); // 每行记录字段数，防止有些行，有很多空记录
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<String> linked = new LinkedList<>();
            for (int j = row.getFirstCellNum(); j < recordValueSize; j++) {
                XSSFCell cell = row.getCell(j);
                if (cell == null) {
                    linked.add("");
                } else {
                    String value = cell.toString();
                    String valueInt = parseInt(value);
                    if (valueInt != null) {
                        linked.add(valueInt);
                    } else {
                        linked.add(value);
                    }
                }
            }
            list.add(linked);
        }
        return list;
    }

    /**
     * 特殊方法。将象"123.0"类型的字符串的".0"去掉<br />
     * 若不符合这样的条件，返回null
     *
     * @param value 字符串
     * @return 字符串
     */
    private static String parseInt(String value) {
        String[] strs = value.split("\\.");
        if (strs.length != 2) {
            return null;
        }
        if (StringUtils.isNumeric(strs[0]) && PATTERN_ZEROS.matcher(strs[1]).matches()) {
            return strs[0];
        }
        return null;
    }

}